--테이블 : 학교 (school)
--속성   : 
--	학교ID - school_id : number(3)  : Primary Key
--	교명 - school_name : varchar2(50) : not null
--	주소 - school_address : varchar2(100) : not null
--	전화번호 - school_tel : varchar2(20) : not null
--	교화 - school_flower : varchar2(50) 
--	학생수 - enrolment : number(6) : not null
-- 
drop table school cascade constraint;

select * from school;

create table school(
	school_id number(3) constraint school_pk primary key,
	school_name varchar2(50) not null,
	school_address varchar2(100) not null,
	school_tel varchar2(20) not null,
	school_flower varchar2(50),
	enrollment number(6) not null
);

insert into school values(1, '경기고', '서초구', '010-1111-1111', '장미', 30);
insert into school values(2, '세화여고', '평택구', '010-2222-2222', null, 40);
insert into school values(3, '세화고', '노원구', '010-3333-3333', '개나리', 40);
insert into school values(4, '서울고', '처인구', '010-4444-4444', '튤립', 50);
insert into school values(5, '서초고', '서초구', '010-5555-5555', '백합', 50);
insert into school values(6, '반포고', '노원구', '010-6666-6666', null, 30);
insert into school values(7, '방배중', '평택구', '010-7777-7777', '장미', 20);

select * from school;
-- department_no에 넣어줄 숫자를 생성해줄 Sequence객체 생성

drop sequence school_seq;

create sequence school_seq
nocache;

select school_seq.nextval from dual;

create table student(
	student_id number(3) constraint student_pk primary key,
	student_name varchar2(50) not null,
	gender varchar2(10) not null,
	school_id number(3),
	constraint student_fk foreign key(school_id) references school(school_id) on delete cascade
);

select * from student;

drop table student cascade constraint;

insert into student values(1, '오인선', '여', 1);
insert into student values(2, '서지원', '남', 1);
insert into student values(3, '정상길', '남', 2);
insert into student values(4, '방지선', '여', 2);
